package database;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.logging.Logger;

import util.LoggerUtil;

/**
 * Dao for the Football Stats Database
 * @author Sion
 * see
 */
public class FsmDao {
	
	private static Logger LOGGER = LoggerUtil.getLogger(FsmDao.class.getName());
	private static String DB_HOME = "C:/ProgramData/FootballStatsManager";
	
	private Properties dbProperties;
	private String dbName;
	private Connection dbConnection;
	private boolean isConnected;
	
	public FsmDao(String fsmDbName) {
	    this.dbName = fsmDbName;
	    setDBSystemDir();
	    dbProperties = loadDBProperties();
	    String driverName = dbProperties.getProperty("derby.driver"); 
	    loadDatabaseDriver(driverName);
        if(!dbExists()) {
        	LOGGER.fine("Database does not exist, creating");
            createDatabase();
        }
        // TODO think about how to handle db changes if new version installed
	}

    /**
     * Returns url of the database
     * @return
     */
    public String getDatabaseUrl() {
        return dbProperties.getProperty("derby.url") + dbName;
    }
	
    /**
     * Returns database location
     * @return
     */
    public String getDatabaseLocation() {
    	return System.getProperty("derby.system.home") + "/" + dbName;
    }
  
    /**
     * Connect to database and prepare statements
     * @return
     */
    public boolean connect() {
        String dbUrl = getDatabaseUrl();
        try {
            dbConnection = DriverManager.getConnection(dbUrl, dbProperties);
            /*
            stmtSaveNewRecord = dbConnection.prepareStatement(strSaveAddress, Statement.RETURN_GENERATED_KEYS);
            stmtUpdateExistingRecord = dbConnection.prepareStatement(strUpdateAddress);
            stmtGetAddress = dbConnection.prepareStatement(strGetAddress);
            stmtDeleteAddress = dbConnection.prepareStatement(strDeleteAddress);
            */
            isConnected = dbConnection != null;
        } catch (SQLException ex) {
            isConnected = false;
        }
        return isConnected;
    }
    
    private void setDBSystemDir() {
        String systemDir = DB_HOME + "/.database";
        System.setProperty("derby.system.home", systemDir);
        File fileSystemDir = new File(systemDir);
        fileSystemDir.mkdir();
        LOGGER.fine("Set up derby system home property as: " + systemDir);
    }
	
	private Properties loadDBProperties() {
	    InputStream dbPropInputStream = null;
	    dbPropInputStream = FsmDao.class.getResourceAsStream("Configuration.properties");
	    dbProperties = new Properties();
	    try {
	        dbProperties.load(dbPropInputStream);
	        LOGGER.fine("Configuration.properties loaded");
	    } catch (IOException e) {
	        LOGGER.severe("Error loading Configuration.properties: " + e);
	    }
	    return dbProperties;
	}

	private void loadDatabaseDriver(String driverName) {
		try {
			Class.forName(driverName);
			LOGGER.fine("Apache Derby Driver loaded");
		} catch (ClassNotFoundException e) {
			LOGGER.severe("Error loading database driver: " + e);
		}
	}
	
    private boolean createDatabase() {
    	boolean dbCreated = false;
        Connection dbConnection = null;
        String dbUrl = getDatabaseUrl();
        dbProperties.put("create", "true");
        try {
            dbConnection = DriverManager.getConnection(dbUrl, dbProperties);
            dbCreated = createTables(dbConnection);
            LOGGER.fine("Database created");
        } catch (SQLException e) {
        	LOGGER.severe("Error creating database: " + e);
        } 
        dbProperties.remove("create");
        return dbCreated;
    }
    
	private boolean dbExists() {
		String dbLocation = getDatabaseLocation();
		File dbFileDir = new File(dbLocation);
		return dbFileDir.exists();
	}
	
    private boolean createTables(Connection dbConnection) {
        boolean createdTables = false;
        Statement statement = null;
        try {
            statement = dbConnection.createStatement();
            statement.execute(CreateDbStatements.createTablePlayer);
            createdTables = true;
            LOGGER.info("Created tables");
        } catch (SQLException e) {
            LOGGER.severe("Error creating tables: " + e);
        }
        return createdTables;
    }
}
